package cn.fastoo.service;

import cn.fastoo.common.entity.FastooMtDetail;
import cn.fastoo.common.entity.Smsrecord;
import cn.fastoo.common.entity.UserBase;
import org.apache.commons.lang3.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Component;
import org.springframework.transaction.annotation.Transactional;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.regex.Matcher;
import java.util.regex.Pattern;


@Component
@Transactional
public class JdbcTemplateService {

    private static Logger logger = LoggerFactory.getLogger(JdbcTemplateService.class);

    @Autowired
    private JdbcTemplate jdbcTemplate;


    public UserBase getUserBase(String apiKey) {
        UserBase userBase = null;
        List<UserBase> userBaseList = jdbcTemplate.query("select b.id,b.balance,b.login_name,a.api_key,a.sign_name,a.sign_status,b.ip_enable,a.need_dr,a.dr_url from user_account a LEFT JOIN user_base b ON a.user_base_id = b.id where a.api_key = ?", new Object[]{apiKey}, new RowMapper<UserBase>() {
            @Override
            public UserBase mapRow(ResultSet resultSet, int i) throws SQLException {
                UserBase userBase = new UserBase();
                userBase.setId(resultSet.getLong("id"));
                userBase.setBalance(resultSet.getDouble("balance"));
                userBase.setLoginName(resultSet.getString("login_name"));
                userBase.setApiKey(resultSet.getString("api_key"));
                userBase.setSignName(resultSet.getString("sign_name"));
                userBase.setSignStatus(resultSet.getInt("sign_status"));
                userBase.setIpEnable(resultSet.getInt("ip_enable"));
                userBase.setNeedDr(resultSet.getInt("need_dr"));
                userBase.setDrUrl(resultSet.getString("dr_url"));
                return userBase;
            }
        });
        if (userBaseList!=null&&userBaseList.size()>0) {
            userBase = userBaseList.get(0);
        }
        return userBase;
    }


    public List<String> getIpList(Long userBaseId) {
        return  jdbcTemplate.query("select ip from user_iplist where user_base_id = ?",
                new Object[]{userBaseId}, new RowMapper<String>() {
            @Override
            public String mapRow(ResultSet resultSet, int i) throws SQLException {
                return resultSet.getString("ip");
            }
        });
    }

    public List<Long> findFixedTimeList(int status, String nowTimeStr) {
        return  jdbcTemplate.query("select id from batch where status = ? AND date_format(timing, '%Y-%m-%d %H:%i') = ?",
                new Object[]{status,nowTimeStr}, new RowMapper<Long>() {
                    @Override
                    public Long mapRow(ResultSet resultSet, int i) throws SQLException {
                        return resultSet.getLong("id");
                    }
                });
    }

    public boolean verifyTemplate(String apiKey, String msg) {
        if (StringUtils.isNotBlank(apiKey)) {
            try {
                List<String> templates = jdbcTemplate.query(
                        "select template_content from user_smstemplate where api_key = ? and state = 1",
                        new Object[]{apiKey},
                        new RowMapper<String>() {
                            @Override
                            public String mapRow(ResultSet rs, int rownumber) throws SQLException {
                                return rs.getString("template_content");
                            }
                        }
                );
                if (templates.size() < 1) {
                    return false;
                } else {
                    //匹配模板
                    return matchTemplate(templates,msg);
                }
            } catch (EmptyResultDataAccessException e) {
                return false;
            }
        }
        return false;
    }

    public static boolean matchTemplate(List<String> templates,String sm) {
        for (String template :templates) {
            if(match(sm,template)){
                return true;
            }
        }
        return false;
    }
    public static Boolean match(String sm,String template) {
        Pattern p = Pattern.compile("("+template.replaceAll("@","[^。]*")+")");
        Matcher m = p.matcher(sm);
        return m.matches();
    }

    public static void main(String[] args) throws Exception{
        List<String> list = new ArrayList<String>();
        list.add("您的验证码是:@,5分钟内有效。");
        list.add("你的验证码是:@");

        System.out.println(matchTemplate(list,"您的验证码是:asdsdad,5分钟内有效。"));

        System.out.println(matchTemplate(list,"的验证码是:asdsdad"));


    }

    public List<FastooMtDetail> findLastMonthRecords(String loginName, Date startDate, Date endDate,String selectApiKey,String destAddr) {
        String sql = "select dest_addr,msg_content,api_key,submit_time,country_code,mt_status,dr_status,fee_count,price from fastoo_mtdetail where login_name = ? AND submit_time >= ? AND submit_time <= ?";
        Object[] objArr = new Object[]{loginName,startDate,endDate};
        if (StringUtils.isNotBlank(selectApiKey) && StringUtils.isBlank(destAddr)) {
            sql = "select dest_addr,msg_content,api_key,submit_time,country_code,mt_status,dr_status,fee_count,price from fastoo_mtdetail where login_name = ? AND submit_time >= ? AND submit_time <= ? AND api_key = ?";
            objArr = new Object[]{loginName,startDate,endDate,selectApiKey};
        } else if (StringUtils.isBlank(selectApiKey) && StringUtils.isNotBlank(destAddr)) {
            sql = "select dest_addr,msg_content,api_key,submit_time,country_code,mt_status,dr_status,fee_count,price from fastoo_mtdetail where login_name = ? AND submit_time >= ? AND submit_time <= ? AND dest_addr = ?";
            objArr = new Object[]{loginName,startDate,endDate,destAddr};
        } else if (StringUtils.isNotBlank(selectApiKey) && StringUtils.isNotBlank(destAddr)) {
            sql = "select dest_addr,msg_content,api_key,submit_time,country_code,mt_status,dr_status,fee_count,price from fastoo_mtdetail where login_name = ? AND submit_time >= ? AND submit_time <= ? AND api_key = ? AND dest_addr = ?";
            objArr = new Object[]{loginName,startDate,endDate,selectApiKey,destAddr};
        }
        return jdbcTemplate.query(sql,objArr, new RowMapper<FastooMtDetail>() {
            @Override
            public FastooMtDetail mapRow(ResultSet resultSet, int i) throws SQLException {
                FastooMtDetail mtDetail = new FastooMtDetail();
                mtDetail.setDestAddr(resultSet.getString("dest_addr"));
                mtDetail.setSubmitTime(resultSet.getTimestamp("submit_time"));
                mtDetail.setMsgContent(resultSet.getString("msg_content"));
                mtDetail.setApiKey(resultSet.getString("api_key"));
                mtDetail.setCountryCode(resultSet.getString("country_code"));
                mtDetail.setMtStatus(resultSet.getInt("mt_status"));
                mtDetail.setDrStatus(resultSet.getInt("dr_status"));
                mtDetail.setFeeCount(resultSet.getInt("fee_count"));
                mtDetail.setPrice(resultSet.getDouble("price"));
                return mtDetail;
            }
        });
    }

    public List<Smsrecord> findRecordsByBatchId(Long batchId) {
        return jdbcTemplate.query("select dest_addr,msg_content,api_key,submit_time,country_code,mt_status,dr_status,fee_count,price from smsrecord where batch_id = ? "
                , new Object[]{batchId}, new RowMapper<Smsrecord>() {
                    @Override
                    public Smsrecord mapRow(ResultSet resultSet, int i) throws SQLException {
                        Smsrecord smsrecord = new Smsrecord();
                        smsrecord.setDestAddr(resultSet.getString("dest_addr"));
                        smsrecord.setSubmitTime(resultSet.getDate("submit_time"));
                        smsrecord.setMsgContent(resultSet.getString("msg_content"));
                        smsrecord.setApiKey(resultSet.getString("api_key"));
                        smsrecord.setCountryCode(resultSet.getString("country_code"));
                        smsrecord.setMtStatus(resultSet.getInt("mt_status"));
                        smsrecord.setDrStatus(resultSet.getInt("dr_status"));
                        smsrecord.setFeeCount(resultSet.getInt("fee_count"));
                        smsrecord.setPrice(resultSet.getDouble("price"));
                        return smsrecord;
                    }
                });
    }

    public void updateSmsrecordDrStatus(String fastooMsgid, String da, Integer drStatus, String drStatuscode) {
        jdbcTemplate.update("UPDATE smsrecord SET dr_status = ?,dr_statuscode=? WHERE fastoo_msgid = ? AND  dest_addr = ?",new Object[]{drStatus,drStatuscode,fastooMsgid,da});
    }

}
